Private Declare Function timeGetTime Lib "winmm.dll" () As Long

 Function getRs(fn)
    Set cc = Word.Application.Documents.Open(fn)
  
     Open "C:\tttt.txt" For Output As #1
    
    'define word table object
    
    
    docName = cc.Name
    Dim tdata As Table
    Dim sql(50) As String
    Set tdata = cc.Tables(1)
    
    'define recordset and connection string
    Dim cn As New ADODB.Connection
    'Dim rs As New ADODB.Recordset
    Dim cnStr As String
    
     'open database connection
    cnStr = "Provider = MSDAORA.1;Password=" & "lisro" & ";User ID=" & "lisro" & ";Data Source=" & "21db" & ";Persist Security Info=True"
    cn.Open cnStr
    
    ' loop 50 times to get all the tables in one word document
    n = 1
    For i = 1 To 50
        ' judge if table content is not null and start from select then do sth.
        If Mid(Trim(tdata.Cell(i, 3).Range.Text), 1, 6) = "select" Then
            
            ' get rid of the acsii code , same as trim()
            sql(n) = (Mid(cc.Tables(1).Cell(i, 3).Range.Text, 1, _
                Len(cc.Tables(1).Cell(i, 3).Range.Text) - 2))

            ' get execution time range
            a = timeGetTime
            cn.Execute sql(n)
            b = timeGetTime
            execTime = b - a
            
            'get execution plain result
            'cn.Execute "explain plan for" & sql(n)
            'rs = cn.Execute("select * from wzp_project")
            
            'MsgBox "explain plan for " & sql(n)
            
            Tstr = ""
            cn.Execute "explain plan for " & sql(n)
            
           
                tSql = "select * from (select * from plan_table   order by 2   desc) where rownum <=2"
                'output sql to a file
                Print #1, tSql
                
               
                If cn.Execute(tSql).RecordCount Then
                    For nn = 0 To 28
                            
                            Tv = cn.Execute(tSql).Fields(nn).Value
                            Tn = cn.Execute(tSql).Fields(nn).Name
                            
                            Tstr = Tstr & " | " & Tn & "->" & Tv & " | "
                    Next nn
                End If
           
            
            
            
            
            'Print #1, Tstr
            
            
            'trim the sql to store at databse
            sqlT = Replace(sql(n), "'", "''")
            
           'store exection time and the according sql into a file with handler #1
           ' Print #1, "execTime : " & execTime
            'Print #1, "Sql Clause: " & sqlT
            'Print #1, ""
            
        'cn.Execute "insert into sqlchange values ('" & Mid(docName, 1, 7) & "', '" & n & "' , '" & sqlT & "',  '" & execTime & "' , '" & Tstr & "')"
         MsgBox "insert into sqlchange values ('" & Mid(docName, 1, 7) & "', '" & n & "' , '" & sqlT & "',  '" & execTime & "' , '" & Tstr & "')"
            n = n + 1
            
        End If
    Next i
    Close #1
   cc.Close
End Function


Sub aa(fn)
    getRs (fn)
End Sub
